package com.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.entity.Deptrole;
import com.util.DBUtil;

public class AdminDao {

	public static int count = 0;
	
	public static ArrayList<Deptrole> getDeptList(int page,int limit){
		ArrayList<Deptrole> deptlist = new ArrayList<Deptrole>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = "select * from deptrole limit ?,?";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setInt(1, page);
			pst.setInt(2, limit);
			rs = pst.executeQuery();
			while(rs.next()) {
				Deptrole deptrole = new Deptrole();
				deptrole.setDeptid(rs.getInt("Deptid"));
				deptrole.setRoleid(rs.getInt("Roleid"));
				deptrole.setDeptname(rs.getString("Deptname"));
				deptrole.setRolename(rs.getString("Rolename"));
				deptrole.setBasewages(rs.getFloat("Basewages"));
				deptrole.setOvertimestandard(rs.getFloat("OvertimeStandard"));
				deptrole.setLeavestandard(rs.getFloat("Leavestandard"));
				deptrole.setAbsentstandard(rs.getFloat("Absentstandard"));
				deptrole.setCreatetime(rs.getString("Createtime"));
				deptrole.setUpdatetime(rs.getString("Updatetime"));
				deptlist.add(deptrole);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		try {
			sql = "select * from deptrole";
			pst = DBUtil.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();
			rs.last();
			count = rs.getRow();
			System.out.println("count:"+count);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return deptlist;
	}
	//修改部门名等
	public static boolean updateDeptroleInfo(int deptid,int roleid,String field,Object newinfo) {
		boolean flag = false;
		PreparedStatement pst = null;
		String sql = null;
		try {
			switch (field) {
			case "deptname":
				sql = "update deptrole set deptname=? where deptid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setObject(1, newinfo);
				pst.setInt(2, deptid);
				break;
			case "basewages":
				sql = "update deptrole set basewages=? where deptid=? and roleid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setObject(1, newinfo);
				pst.setInt(2, deptid);
				pst.setInt(3, roleid);
				break;
			case "overtimestandard":
				sql = "update deptrole set overtimeStandard=? where deptid=? and roleid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setObject(1, newinfo);
				pst.setInt(2, deptid);
				pst.setInt(3, roleid);
				break;
			case "leavestandard":
				sql = "update deptrole set leavestandard=? where deptid=? and roleid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setObject(1, newinfo);
				pst.setInt(2, deptid);
				pst.setInt(3, roleid);
				break;
			case "absentstandard":
				sql = "update deptrole set absentstandard=? where deptid=? and roleid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setObject(1, newinfo);
				pst.setInt(2, deptid);
				pst.setInt(3, roleid);
				break;
			}
			flag = (pst.executeUpdate()!=0);
			System.out.println("pst:"+pst);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	//删除部门
	public static boolean deleteDept(int deptid) {
		boolean action = false;
		PreparedStatement pst = null;
		String sql = "delete from deptrole where deptid=?";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setInt(1, deptid);
			action = (pst.executeUpdate()!=0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return action;
	}
	//新建部门
	public static boolean deptroleadd(String deptname,int deptid,int roleid,String rolename,float basewages,float overtimestandard,float leavestandard,float absentstandard) {
		boolean action = false;
		PreparedStatement pst = null;
		String sql = "";
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
		String nowtime = df.format(new Date());// new Date()为获取当前系统时间
		try {
			if(roleid == 1) {
				sql = "insert into deptrole(deptname,roleid,rolename,"
						+ "basewages,overtimestandard,leavestandard,"
						+ "absentstandard,createtime)"
						+ "value(?,?,?,?,?,?,?,?);";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, deptname);
				pst.setInt(2, roleid);
				pst.setString(3, rolename);
				pst.setFloat(4, basewages);
				pst.setFloat(5, overtimestandard);
				pst.setFloat(6, leavestandard);
				pst.setFloat(7, absentstandard);
				pst.setString(8, nowtime);
				action = (pst.executeUpdate()!=0);
			}
			else if(roleid == 2){
				sql = "insert into deptrole(deptname,roleid,rolename,"
						+ "basewages,overtimestandard,leavestandard,"
						+ "absentstandard,deptid,createtime)"
						+ "value(?,?,?,?,?,?,?,?,?);";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, deptname);
				pst.setInt(2, roleid);
				pst.setString(3, rolename);
				pst.setFloat(4, basewages);
				pst.setFloat(5, overtimestandard);
				pst.setFloat(6, leavestandard);
				pst.setFloat(7, absentstandard);
				pst.setInt(8, deptid);
				pst.setString(9, nowtime);
				action = (pst.executeUpdate()!=0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return action;
	}
	public static boolean isExit(int deptid) {
		boolean action = false;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = "select * from deptrole where deptid=?";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setInt(1, deptid);
			rs = pst.executeQuery();
			if(rs.next()) {
				action = true;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return action;
	}
}
